/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;
import ModelLayer.*;
import java.util.ArrayList;
import java.sql.*;
/**
 *
 * @author Stefan
 */
public class DBInvoice implements IFDBInvoice
{
    private Connection con;

    public DBInvoice()
    {
            con = DBConnection.getInstance().getDBcon();
    }



    private ArrayList<Invoice> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
		ArrayList<Invoice> list = new ArrayList<Invoice>();
        String query =  buildQuery(wClause);
        try{ // read the invoice from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            while(results.next() )
            {
	     	 Invoice invoice = new Invoice();
		 invoice = buildInvoice(results);
                 list.add(invoice);
            }//end while
            stmt.close();
        }//slut try
        catch(Exception e)
        {
            System.out.println("Query exception - select: "+e);
            e.printStackTrace();
        }
            return list;
    }

    private Invoice singleWhere(String wClause, boolean retriveAssociation)
    {
        ResultSet results;
        Invoice invoice = new Invoice();

        String query =  buildQuery(wClause);
        System.out.println(query);
        try{ // read the invoice from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if( results.next() )
            {
                invoice = buildInvoice(results);
                //assocaition is to be build
                stmt.close();
            }
            else{ //no invoice was found
                invoice = null;
            }
        }//end try
        catch(Exception e){
            System.out.println("Query exception: "+e);
        }
        return invoice;
    }

    private String buildQuery(String wClause)
    {
        String query = "SELECT invoiceNo, paymentDate, paymentMethod, paymentType, status FROM Invoice";

        if (wClause.length() > 0)
        {
                query = query + " WHERE " + wClause;
        }

        return query;
    }

    private Invoice buildInvoice(ResultSet results)
    {
        Invoice invoice = new Invoice();
        DBSalesOrder dbsale = new DBSalesOrder();

        try
        {
                invoice.setInvoiceNo(results.getInt("invoiceNo"));
                invoice.setPaymentDate(results.getString("paymentDate"));
                invoice.setPaymentMethod(results.getInt("paymentMethod"));
                invoice.setPaymentType(results.getInt("paymentType"));
                invoice.setStatus(results.getInt("status"));
                invoice.setOrders(dbsale.getInvoiceSalesOrders(results.getInt("invoiceNo")));
        }
        catch (Exception e)
        {
                System.out.println("error in building the invoice object");
        }
        return invoice;
    }

    public ArrayList<Invoice> getAllInvoices(boolean retriveAssociation) {
        return miscWhere("", retriveAssociation);
    }

    public Invoice findInvoice(int id, boolean retriveAssociation) {
        String wClause = "  invoiceNo = '" + id + "'";
        return singleWhere(wClause, retriveAssociation);
    }

    public int insertInvoice(Invoice invoice) {
                //call to get the next invoice no
        int nextID = GetMax.getMaxId("Select max(invoiceNo) from invoice");
        nextID = nextID + 1;
        System.out.println("next invoiceNo = " +  nextID);

        int rc = -1;
	String query="INSERT INTO invoice(invoiceNo, paymentDate, paymentType, status, paymentMethod)  VALUES('"+
	  	nextID + "','" +
                invoice.getPaymentDate()  + "','"  +
		invoice.getPaymentType()  + "','"  +
                invoice.getStatus() + "','" +
                invoice.getPaymentMethod() + "')";
       System.out.println("insert : " + query);
       try{ // insert new invoice
          con.setAutoCommit(false);
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
          con.commit();
          con.setAutoCommit(true);
       }//end try
       catch(SQLException ex){
          System.out.println("Invoice ikke oprettet");
          System.out.println("Insert exception in invoice db: "+ex);
	  try{
             rc=-1;
             con.rollback();
             con.setAutoCommit(true);
          }
          catch(Exception ex1)
          {
             System.out.println("Error rolling back"+ ex1);
          }
       }
        return(rc);
    }

    public int updateInvoice(Invoice invoice) {
            Invoice voice  = invoice;
            int rc=-1;

            String query="UPDATE invoice SET "+
                      "paymentDate ='"+ voice.getPaymentDate() +"', "+
                      "paymentType ='"+ voice.getPaymentType() + "', " +
                      "status ='"+ voice.getStatus() + "', " +
                      "paymentMethod ='"+ voice.getPaymentMethod() + "', " +
                      " WHERE invoiceNo = '"+ voice.getInvoiceNo() + "'";
            System.out.println("Update query:" + query);
            try{ // update invoice
                    Statement stmt = con.createStatement();
                    stmt.setQueryTimeout(5);
                    rc = stmt.executeUpdate(query);

                    stmt.close();
            }//slut try
            catch(Exception ex){
                    System.out.println("Update exception in invoice db: "+ex);
            }
            return(rc);
    }

    public int deleteInvoice(int id) {
        int rc=-1;
        String query="DELETE FROM invoice WHERE invoiceNo = '" +
                        id + "'";
        System.out.println(query);
        try{ // delete from invoice
                con.setAutoCommit(false);
                Statement stmt = con.createStatement();
                stmt.setQueryTimeout(5);
                rc = stmt.executeUpdate(query);
                stmt.close();
                con.commit();
                con.setAutoCommit(true);
        }//slut try
        catch(Exception ex){
                System.out.println("Delete exception in invoice db: "+ex);

                try{
                    rc=-1;
                    con.rollback();
                    con.setAutoCommit(true);
                }
                catch(Exception ex1)
                {
                    System.out.println("Error rolling back" + ex1);
                }
        }
        return(rc);
    }
}
